/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package usl;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.NamingException;
import javax.servlet.jsp.jstl.sql.ResultSupport;


import connezione.PooledConnection;

import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.util.HashMap;
import java.util.Map;
import java.util.TreeMap;
import javax.faces.context.FacesContext;
import javax.servlet.jsp.jstl.sql.Result;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.xpath.XPathExpressionException;
import login.BeanUser;


import javax.mail.MessagingException;
import javax.servlet.ServletContext;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpression;
import javax.xml.xpath.XPathFactory;
import mail.Mail;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

/**
 *
 * @author ren
 */
public class beanUsl {

    private String tipomail;
    private String usermail;
    private String passmail;
    

    /** Creates a new instance of beanUsl */
    public beanUsl() {
        conn = (PooledConnection) ((BeanUser) FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get("user")).getCon();
        user = ((BeanUser) FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get("user"));

    }
    PooledConnection conn;
    BeanUser user;

    public Result getPazmedico() throws SQLException, NamingException {
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT p.nome, p.cognome, p2.nome as nomemedico, p2.cognome as cognomemedico From Persona as p, Persona as p2 where p.id_medico = p2.id");
        return ResultSupport.toResult(result);
    }

    public Result getListarichiami() throws SQLException, NamingException {
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery(" Select p.id as id_persona, p.nome, p.cognome, p.e_mail, max(v.richiami) as max_richiami from Visita as v, Persona as p where v.richiami >= 1 and v.id_paziente = p.id group by id_persona");
        return ResultSupport.toResult(result);
    }

    private void aggiornaDbRichiami(int idpaz) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("UPDATE Visita SET richiami = richiami + 1 WHERE id > 0 AND richiami >= 0 AND id_paziente =" + idpaz);
    }

    private void inviaMailRichiamo(int idpaz) throws SQLException, MessagingException, IOException {



        //recupera dati paziente
        String nome, cognome, email, testo_email, id_capo_villaggio;
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT nome, cognome, e_mail, id_capo_villaggio FROM Anagrafica WHERE id=" + idpaz);
        result.next();
        nome = result.getString("nome");
        cognome = result.getString("cognome");
        email = result.getString("e_mail");
        id_capo_villaggio = result.getString("id_capo_villaggio");

        //inserisce visite paziente nel testo email
        testo_email = "Gentile Signore\\a " + nome + " " + cognome + ", è pregato\\a di recarsi all'USL di competenza per effettuare le seguenti visite: \n\n";

        result = stmt.executeQuery("SELECT * FROM Visita WHERE richiami >= 0 AND id_paziente = " + idpaz + " order by data_scadenza asc");
        while (result.next()) {

            testo_email = testo_email + "Data: " + result.getString("data_scadenza") + " Tipo visita: " + result.getString("tipo_visita") + " Tipo scadenza: " + result.getString("tipo_scadenza") + " ";

            int k = result.getInt("richiami");

            if (k > 1) {
                testo_email = testo_email + "QUESTO E' IL " + k + " RICHIAMO! \n";
            } else {
                testo_email = testo_email + "\n";
            }






        }


        Mail msg = new Mail(email, testo_email, usermail, passmail, tipomail, "RICHIAMO");
        new Thread(msg).start();

        ServletContext servletContext = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();

        String realpath = servletContext.getRealPath("/temp");


        PrintStream out = new PrintStream(new FileOutputStream(new File(realpath, id_capo_villaggio + ".txt"), true));


        out.println(nome + "  " + cognome);

        out.flush();
        out.close();








    }

    public String inviaRichiami() throws SQLException, MessagingException, IOException {

        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT distinct id_paziente FROM Visita WHERE richiami >= 0");
        while (result.next()) {
            inviaMailRichiamo(result.getInt("id_paziente"));
            aggiornaDbRichiami(result.getInt("id_paziente"));
        }


        result = stmt.executeQuery("SELECT distinct id_capo_villaggio, e_mail FROM Anagrafica");
        while (result.next()) {

            String id_capo = result.getString("id_capo_villaggio");
            String dest = result.getString("e_mail");

            ServletContext servletContext = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();

            String realpath = servletContext.getRealPath("/temp");



            File f = new File(realpath, id_capo + ".txt");

            if (f.exists()) {

                Mail m = new Mail(dest, "Trova la lista dei pazienti in allegato.", f, usermail, passmail, tipomail, "Lista Richiamati");
                new Thread(m).start();



            }


        }

        return "richiamiOk";
    }

    /**
     * @return the tipomail
     */
    public String getTipomail() {
        return tipomail;
    }

    /**
     * @param tipomail the tipomail to set
     */
    public void setTipomail(String tipomail) {
        this.tipomail = tipomail;
    }

    /**
     * @param usermail the usermail to set
     */
    public void setUsermail(String usermail) {
        this.usermail = usermail;
    }

    /**
     * @param passmail the passmail to set
     */
    public void setPassmail(String passmail) {
        this.passmail = passmail;
    }

    /**
     * @return the usermail
     */
    public String getUsermail() {
        return usermail;
    }

    /**
     * @return the passmail
     */
    public String getPassmail() {
        return passmail;
    }
    
    
   
      
      
       public TreeMap<String, String> getSenzaCoord() throws SQLException, NamingException {
        TreeMap<String, String> myModelValues = new TreeMap<String, String>();
        Statement stmt = conn.createStatement();
          ResultSet result = stmt.executeQuery("SELECT id, nome, cognome From Anagrafica where latitudine is null AND longitudine is null");
 
        while (result.next()) {
            myModelValues.put(result.getString(2) + " " + result.getString(3), result.getString(1));

        }
        return myModelValues;
    }
      
      
      

      private String idCoord;
      

      
    public String updCoord() throws ParserConfigurationException, SAXException, IOException {
        int id = Integer.valueOf(idCoord);
        String luogoNascita;
        Statement stmt;
        String latitudine, longitudine = "";
        try {
            try {
                stmt = conn.createStatement();
                ResultSet result = stmt.executeQuery("SELECT luogo_nascita FROM Anagrafica WHERE id=" + id);

                result.next();
                luogoNascita = result.getString("luogo_nascita");


                DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
                factory.setNamespaceAware(true); // never forget this!
                DocumentBuilder builder = factory.newDocumentBuilder();
                Document doc = builder.parse("http://ws.geonames.org/search?q=" + luogoNascita + "&maxRows=1&lang=it");
                XPathFactory xpfactory = XPathFactory.newInstance();
                XPath xpath = xpfactory.newXPath();
                XPathExpression expr = xpath.compile("//lat/text()");
                Object xpresult = expr.evaluate(doc, XPathConstants.NODESET);
                NodeList nodes = (NodeList) xpresult;
                latitudine = nodes.item(0).getNodeValue();
                expr = xpath.compile("//lng/text()");
                xpresult = expr.evaluate(doc, XPathConstants.NODESET);
                nodes = (NodeList) xpresult;
                longitudine= nodes.item(0).getNodeValue();

                stmt.executeUpdate("UPDATE Anagrafica SET `latitudine`='"+latitudine + "', `longitudine`='"+ longitudine +"' WHERE `id`='"+id+"'");
                
 

            } catch (XPathExpressionException ex) {
                Logger.getLogger(beanUsl.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (SQLException ex) {
            Logger.getLogger(beanUsl.class.getName()).log(Level.SEVERE, null, ex);
        }




        return "back";
    }

    /**
     * @return the idCoord
     */
    public String getIdCoord() {
        return idCoord;
    }

    /**
     * @param idCoord the idCoord to set
     */
    public void setIdCoord(String idCoord) {
        this.idCoord = idCoord;
    }
}
